import pandas as pd
from tabulate import tabulate

from infrastructure.utils.json import json_marshal, json_unmarshal


class ExcelReader:

    @classmethod
    def read_excel_as_json_array(cls, file: str | bytes) -> list:
        # header=None 不让第一行作为列名
        df = pd.read_excel(file, header=None)
        df_filled = df.fillna('')
        # 转换为JSON二维数据（列表的列表）
        json_array = list(df_filled.values.tolist())
        cls.remove_empty_element(json_array)
        return json_array

    @classmethod
    def read_excel_to_split_markdown_json_doc(cls, file: str | bytes) -> str:
        json_array = cls.read_excel_as_json_array(file)
        config_item_list = cls.split_json_array(json_array)
        markdown_content_list = []
        for config_item in config_item_list:
            md = cls.config_item_convert_to_md(config_item)
            markdown_content_list.append(md)
        return '\n'.join(markdown_content_list)

    @classmethod
    def split_json_array(cls, json_array: list[list]) -> list[dict[str, list]]:
        """
        将JSON二维数组拆分成每个单独的配置项dict
        {
            "item_title": "标题",
            "item_json": [
                ["value1", "value2"],
                ["value1", "value2"]
            ]
        }
        """
        result = []
        item_dict = {}
        item_json = []
        for row in json_array:
            # 配置项内容
            if len(row) > 1:
                item_json.append(row)
            # 配置项表头或者分隔行
            else:
                item_title = row[0]
                # 表头
                if item_title:
                    item_dict['item_title'] = item_title
                    item_json.append(row)
                # 分割行
                else:
                    if item_json:
                        item_dict['item_json'] = item_json
                        result.append(item_dict)
                        # clear 重置
                        item_dict, item_json = {}, []
        else:
            if item_json:
                item_dict['item_json'] = item_json
                result.append(item_dict)
        return result

    @classmethod
    def read_excel_as_markdown_table(cls, file: str | bytes) -> str:
        df = pd.read_excel(file, header=None, engine='openpyxl')
        df = df.fillna('')
        return tabulate(
            df.fillna('\t'),
            headers='firstrow',
            showindex=False,
            tablefmt="pipe",
        )

    @classmethod
    def remove_empty_element(cls, json_array: list):
        """去除json数组中的空元素"""
        for row in json_array:
            exist_element_index = -1
            for index, cell in enumerate(row):
                if cell != '':
                    exist_element_index = index
            # 跳过分隔行 eg: [,,,,] -> []
            if exist_element_index == -1:
                row[:] = ['']
                continue
            # 去除当前行中后缀的空元素 eg: [1,2,3,,6,,] -> [1,2,3,,6]
            if exist_element_index < len(row) - 1:
                row[:] = row[:exist_element_index + 1]

    @classmethod
    def export_json_array(cls, json_array: list, file_path: str):
        with open(file_path, 'w', encoding='utf-8') as f:
            f.write(json_marshal(json_array))

    @classmethod
    def to_json_str(cls, json_array: list) -> str:
        return json_unmarshal(json_array)

    @classmethod
    def config_item_convert_to_md(cls, config_item: dict) -> str:
        """
        将配置项转markdown文档
        # title

        ```json
        [[],[],[]]
        ```
        """
        content_list = []
        # 标题
        title = f"# {config_item['item_title']}\n"
        content_list.append(title)
        # 配置项内容
        item_content = f"```json\n{config_item['item_json']}\n```\n"
        content_list.append(item_content)
        return "\n".join(content_list)


if __name__ == '__main__':
    print(ExcelReader.read_excel_to_split_markdown_json_doc("C:\\Users\\Administrator\\Desktop\\美人鱼大促-empty.xlsx"))
